Imports System.IO

Public Class Class_Dataset_to_Excel

    Public Shared Sub DisplayDownloadDialog(ByVal Res As System.Web.HttpResponse, ByVal PathFile As String, ByVal FileName As String)
        Dim strPhysicalPath
        Dim objFileInfo As System.IO.FileInfo
        Try
            strPhysicalPath = PathFile & FileName
            'exit if file does not exist
            If Not System.IO.File.Exists(strPhysicalPath) Then Exit Sub
            objFileInfo = New System.IO.FileInfo(strPhysicalPath)
            Res.Clear()
            'Add Headers to enable dialog display
            Res.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
            Res.AddHeader("Content-Length", objFileInfo.Length.ToString())
            Res.ContentType = "application/octet-stream"
            Res.WriteFile(objFileInfo.FullName)
        Catch
        Finally
            Res.End()
        End Try
    End Sub

    Public Shared Function SaveAsExcel(ByVal DT As DataTable, ByVal PathFile As String, ByVal FileName As String, ByVal Header As String) As Boolean

        Try
            Dim mstrPath As String = ""
            Dim mstrExportPath As String = ""
            Dim msbExportHeader As System.Text.StringBuilder
            Dim msbExportData As System.Text.StringBuilder
            Dim mobjExportWriter As System.IO.StreamWriter

            'Call Sub Delete Old File
            DeleteOldFileWithDay(PathFile, ".XLS", 1)

            If FileName = "" Then
                FileName = Get_Time_Series() & Get_GUID() & ".XLS"
            End If

            mstrExportPath = PathFile & FileName

            mobjExportWriter = New System.IO.StreamWriter(mstrExportPath, True, System.Text.Encoding.Unicode)
            msbExportHeader = New System.Text.StringBuilder

            Dim iCol As Integer
            Dim iRow As Integer
            Dim iCell As Integer
            Dim arrItem As Object

            'Write the columns to the excel spreadsheet 
            Dim XCount As Integer
            XCount = DT.Columns.Count - 1

            msbExportData = New System.Text.StringBuilder
            For iCol = 0 To XCount
                If iCol = 0 Then
                    msbExportData.Append(Header & vbTab)
                Else
                    msbExportData.Append("" & vbTab)
                End If
            Next
            'Write the row to the spreadsheet
            mobjExportWriter.WriteLine(msbExportData.ToString)
            mobjExportWriter.WriteLine("" & vbTab)

            For iCol = 0 To XCount
                msbExportHeader.Append(DT.Columns(iCol).ColumnName & vbTab)
            Next
            'Write the Column Headers to the spreadsheet
            mobjExportWriter.WriteLine(msbExportHeader.ToString)

            'Append the Field Values to the StringBuilder object
            Dim XCount1 As Integer
            XCount1 = DT.Rows.Count - 1
            For iRow = 0 To XCount1

                msbExportData = New System.Text.StringBuilder

                For iCol = 0 To XCount
                    msbExportData.Append(DT.Rows(iRow).Item(iCol) & vbTab)
                Next

                'Write the row to the spreadsheet
                mobjExportWriter.WriteLine(msbExportData.ToString)
            Next

            mobjExportWriter.Close()
            mobjExportWriter = Nothing

            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function DeleteOldFile(ByVal PathFile As String, ByVal FileName As String)
        Try
            Dim Dir1 As New DirectoryInfo(PathFile)
            Dim File1 As FileInfo
            For Each File1 In Dir1.GetFiles
                Dim FName As String = Trim(UCase(File1.Name))
                If Trim(UCase(FileName)) = FName Then
                    File.Delete(PathFile & "\" & FileName)
                End If
            Next
        Catch ex As Exception
        End Try
    End Function

    Public Shared Function DeleteOldFileWithDay(ByVal PathFile As String, ByVal ExtensionName As String, ByVal DateCount As Integer)
        Try
            Dim Dir1 As New DirectoryInfo(PathFile)
            Dim File1 As FileInfo
            For Each File1 In Dir1.GetFiles
                If (UCase(File1.Extension) = UCase(ExtensionName)) And Math.Abs(DateDiff(DateInterval.Day, Now, File1.CreationTime)) >= DateCount Then
                    File.Delete(PathFile & "\" & File1.Name)
                End If
            Next
        Catch ex As Exception
        End Try
    End Function

    Public Shared Function Get_GUID() As String
        Dim ID As New Guid
        Dim str As String
        str = ID.NewGuid.ToString
        str = Replace(str, "-", "")
        Return str
    End Function

    Public Shared Function Get_Time_Series() As String
        Dim str As String
        str = Format(Now, "HHmmss")
        Return str
    End Function

    Public Shared Function Get_Date_Series() As String
        Dim str As String
        str = Format(Now, "yyMMdd")
        Return str
    End Function

End Class
